Totals

Add grand totals and sub-totals to your grids to provide additional context and information. Totals can be enabled either through the right-click context menus or via the Totals dialog box which offers more options for setting and configuring the totaling logic deployed. The dialog is explained below.

  • Click here to learn how to disable total formatting.

Using the Totals Dialog

Quick totals are normally enabled via the context menu and apply only to grid visualizations. The alternative technique is to turn on totals using the ribbon buttons and the associated dialogs. This offers finer control over both the total logic. Toggle the Totals button from the Home or Query ribbon to show or hide totals.

By default, grand totals will be added to the grid. Clicking on the bottom of the Totals split button, users can access more controls, options and settings for totaling columns and rows.

Basic Totals Settings

Using the bottom of the Totals split button will allow users to configure specific options for totals for columns and rows separately (green and blue arrows below).

Use the checkbox to disable totals for either rows or columns completely (red arrow). Beyond this, the following options are available through the drop downs:

Total Position

  • For Columns: options to show totals on the left or right.
  • For Rows: options to show totals on the bottom or top.

Bottom Totals: adds an additional row or column at the end or bottom of the grid - this is the default position.

Top Totals: adds an additional row or column at the start or top of the grid.

Totals Type

Sub-totals cannot function together with sorting, filtering, N-of-N and elimination logic.

Grand Totals Only: displays grand totals only:

Grand and Sub-Totals: displays both grand totals and sub-totals:

Sub Totals: displays sub-totals only:

Sub-Totaling Options

If sub-totaling is enabled (red arrow below) and there are 2 or more attributes in the grid (blur and purple arrows below), a new set of options are available to control which attributes will be sub-totaled (green box below). By default all attributes are sub-totaled.

Total Functions

The totaling math logic is driven by this option for all metrics in the grid.

  • Aggregate (default): uses the aggregation type defined within the source database.
  • Sum: displays the sum of the totaled cells.
  • Count: displays the number of contributing cells. Empty cells are NOT counted.
  • Average: displays the average value of the contributing cells. Empty cells are NOT included in calculation.
  • Minimum: displays the minimum value from among the contributing cells.
  • Maximum: displays the maximum value from among the contributing cells
  • Median: displays the median value of the contributing cells. Empty cells are NOT included in calculation.
  • Standard Deviation: displays the standard deviation between the contributing cells. Empty are NOT included in calculation.

Custom Captions

To create custom totals captions, open the Advanced panel and enter the custom captions:

Advanced Total Options

Advanced formulation options are available on MDX based data sources only. These switches are provided to control the complexities of totaling in MDX cube based technologies.

Logic Options

The way hierarchical data should total in MDX based engines is governed by the 'logic options' (green box below).

  • Bottom-Up "Hierarchical" : When users total multi-level or 'regular' hierarchies, the engine will add up all the leaf level items from the bottom, ignoring any parent elements in the tree. This is the default "hierarchical" logic option. In some situations, this approach might not be appropriate or the desired effect.
  • Top-Down Hierarchical: This will add all the nodes in a hierarchy starting from the top of the hierarchy working its way down, ignoring any child elements of existing parent objects in the tree.
  • Flat: will add up all visible elements int eh tree, regardless of any ancestor or descendant objects.

Formulation Options

Beyond the logic technique, there are a variety of other switches for totaling math in MDX engines (purple box above):

  • Ignore Model Scoping: this informs the query engine to ignore the 'scope isolation' switch in Microsoft OLAP. This changes the behavior of client side calculations vs server side calculations.
  • Include Calculated Member: this informs to include calculated members in totals.
  • Overwrite Solve Order: in situations where there are overlapping calculations, the order in which they are solved can impact the totals. This allows you to specify the solve order for the totals themselves.
  • Intelligent Totals: this switch allows the engine to heuristically decide on the fastest way to add totals to your query. The switch allows you to turn it off in the event the approach makes things slower in your specific circumstance.
  • Force Data Source Totals: The totaling engine will, by default, automatically determine if it needs to calculate totals on the data source engine or if it can be resolved accurately by Pyramid (which is faster). Check this box to override this logic and force all totals to be calculated on the data source only.
  • Optimize Totals: This matches the settings that can be triggered from the query settings menu.
  • Context Heuristics: applies 'EXISTING' functional logic when the attributes used in one of the totals derive from the same dimension of a Microsoft OLAP or Tabular model. For more information on the EXISTING function, please see here.
  • Calculate in Sub Query: splits ach of the totals combinations into multiple queries. Each query will then put the relevant members into the SUB QUERY of the MDX. This can sometimes greatly optimize the performance of the query. This does not work under the following circumstances:
    • When 'Include Calculated Member' is on.
    • When “Flat” is chosen from Logic Options.
    • If any hierarchies in the query are Non-Aggregatable (have no ALL member)